using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using DomesticBank.Models;


namespace DomesticBank.Models {
	public class AccountsData {
		public AccountsData() {
		}
		
		
		public void addTransaction(int accountid, Transaction transaction) {
			string sql = "insert into transactions (name, beneficiary, income, expense, balance, description, category, payment, date, time, accountid)";
			sql += " values (@name, @beneficiary, @income, @expense, @balance, @description, @category, @payment, @date, @time, @accountid)";
				
			MySqlCommand command = new MySqlCommand(sql, Core.Instance.getConnection());
			command.Parameters.Add("@name", MySqlDbType.VarChar);
			command.Parameters.Add("@beneficiary", MySqlDbType.VarChar);
			command.Parameters.Add("@income", MySqlDbType.Decimal);
			command.Parameters.Add("@expense", MySqlDbType.Decimal);
			command.Parameters.Add("@balance", MySqlDbType.Decimal);
			command.Parameters.Add("@description", MySqlDbType.VarChar);
			command.Parameters.Add("@category", MySqlDbType.Int16);
			command.Parameters.Add("@payment", MySqlDbType.Int16);
			command.Parameters.Add("@date", MySqlDbType.Date);
			command.Parameters.Add("@time", MySqlDbType.Time);
			command.Parameters.Add("@accountid", MySqlDbType.Int16);
			
			command.Parameters["@name"].Value = transaction.Name;
			command.Parameters["@beneficiary"].Value = transaction.Beneficiary;
			command.Parameters["@income"].Value = transaction.Income;
			command.Parameters["@expense"].Value = transaction.Expenses;
			command.Parameters["@balance"].Value = transaction.Balance;
			command.Parameters["@description"].Value = transaction.Description;
			command.Parameters["@category"].Value = transaction.Category;
			command.Parameters["@payment"].Value = transaction.Payment;
			command.Parameters["@date"].Value = transaction.Date;
			command.Parameters["@time"].Value = transaction.Time;
			command.Parameters["@accountid"].Value = accountid;
			
			command.ExecuteNonQuery();
			
			Core.Instance.closeConnection();
		}
		
		
		public void addAccount(Account account) {
			string sql = "insert into accounts (name, income, expenses, balance, userid)";
			sql += " values (@name, @income, @expenses, @balance, @userid)";
			
			MySqlCommand command = new MySqlCommand(sql, Core.Instance.getConnection());
			command.Parameters.Add("@name", MySqlDbType.VarChar);
			command.Parameters.Add("@income", MySqlDbType.Decimal);
			command.Parameters.Add("@expenses", MySqlDbType.Decimal);
			command.Parameters.Add("@balance", MySqlDbType.Decimal);
			command.Parameters.Add("@userid", MySqlDbType.Int16);
			
			command.Parameters["@name"].Value = account.Name;
			command.Parameters["@income"].Value = account.Income;
			command.Parameters["@expenses"].Value = account.Expenses;
			command.Parameters["@balance"].Value = account.Balance;
			command.Parameters["@userid"].Value = account.User.Id;
			
			command.ExecuteNonQuery();
			
			Core.Instance.closeConnection();
		}
		
		
		public void deleteAccount(int id) {
			string sql = "delete from accounts where id = @id";
			
			MySqlCommand command = new MySqlCommand(sql, Core.Instance.getConnection());
			command.Parameters.Add("@id", MySqlDbType.Int16);
			
			command.Parameters["@id"].Value = id;
			
			command.ExecuteNonQuery();
			
			Core.Instance.closeConnection();
		}
		
		
		public List<Account> getAccounts(User user) {
			string sql = "select id, name, income, expenses, balance from accounts where userid = @userid";
			
			MySqlCommand command = new MySqlCommand(sql, Core.Instance.getConnection());
			command.Parameters.Add("@userid", MySqlDbType.Int16);
			
			command.Parameters["@userid"].Value = user.Id;
			
			MySqlDataReader reader = command.ExecuteReader();
			
			List<Account> accounts = new List<Account>();
			
			while(reader.Read()) {
				Account account = new Account();
				
				account.Id = (int)reader["id"];
				account.Name = reader["name"].ToString();
				account.Income = (decimal)reader["income"];
				account.Expenses = (decimal)reader["expenses"];
				account.Balance = (decimal)reader["balance"];
				
				accounts.Add(account);
			}
			
			reader.Close();
			Core.Instance.closeConnection();
			
			return accounts;
		}
		
		
		public Account getAccount(int userid, int accountsid) {
			string sql = "select id, name, income, expenses, balance from accounts where userid = @userid and id = @accountsid";
			
			MySqlCommand command = new MySqlCommand(sql, Core.Instance.getConnection());
			command.Parameters.Add("@userid", MySqlDbType.Int16);
			command.Parameters.Add("@accountsid", MySqlDbType.Int16);
			
			command.Parameters["@userid"].Value = userid;
			command.Parameters["@accountsid"].Value = accountsid;
			
			MySqlDataReader reader = command.ExecuteReader();
			
			reader.Read();
			Account account = new Account();
			
			account.Id = (int)reader["id"];
			account.Name = reader["name"].ToString();
			account.Income = (decimal)reader["income"];
			account.Expenses = (decimal)reader["expenses"];
			account.Balance = (decimal)reader["balance"];
				
			reader.Close();
			Core.Instance.closeConnection();
			
			return account;
		}
	}
}